from DataAccess.DBConnFactory import DBConnFactory
from Misc.Utils import *

from string import Template
from datetime import date, timedelta

def calc_pnl(start_date, end_date, tick_list=None, portf_list=None):
	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()							
	
	#
	#NOT DEPENDENT ON TRANSACTION_EX VIEW VERSION
	# sql_tpl = Template('''select ticker,portfolio_id,currency
				# coeff*sum(amount*cost_price)
					# *if(trade_type='BUY' or trade_type='SHORT COVERING' or trade_type='SC',-1,1) pnl	
				# (
				# select trd.ticker,portfolio_id,amount,cost_price,trade_type,currency,
					# if(security_type='FUTURES',s.value,1.0) as coeff
				# from transaction trd
				# left join comm_misc_static_info s 
					# on trd.ticker=s.ticker 
					#   and s.field='FUT_VAL_PT' 
				# where (security_type='STOCK' or security_type='FUTURES')
				# and trade_date>='${START_DATE}' and trade_date<='${END_DATE}'
				# and ${TICK_CRITERIA}
				# UNION
				# select trd.ticker,portfolio_id,amount,cost_price,trade_type,trd.currency,
					# 1.0 as coeff
				# from transaction trd
				# join pnote_info pi on pi.isin_code=trd.ticker 
				# where security_type='PNOTE' 
				# and trade_date>='${START_DATE}' and trade_date<='${END_DATE}'
				# and ${PN_TICK_CRITERIA} 
				# ) t		
				# where ${PORTF_CRITERIA} 
				# group by portfolio_id,ticker,currency
				# ''')
	# sql_text = sql_tpl.substitute(START_DATE=start_date.isoformat(), 
							# END_DATE=end_date.isoformat(),
							# TICK_CRITERIA=format_sql_criteria('trd.ticker', tick_list), 
							# PN_TICK_CRITERIA=format_sql_criteria('pi.underlying_ticker', tick_list), 
							# PORTF_CRITERIA=format_sql_criteria('t.portfolio_id', portf_list))	
	
	#
	#NOT DEPENDENT ON TRANSACTION_EX VIEW VERSION
	###and lp.ref_date=SUBDATE('${DATE}',1)
	# sql_tpl = Template('''select ticker,portfolio_id,curncy,-amount*lp.close_price pnl
				# (
				# select ticker,ticker p_tk,portfolio_id,amount,price_currency curncy,
					# if(security_type='FUTURES',s.value,1.0) as coeff
				# from daily_position dp
				# left join comm_misc_static_info s 
					# on dp.ticker=s.ticker 
					#   and s.field='FUT_VAL_PT' 
				# where ref_date='${DATE}'
				# and (security_type='STOCK' or security_type='FUTURES')
				# and ${TICK_CRITERIA}
				# UNION
				# select dp.ticker,pi.underlying_ticker p_tk,portfolio_id,amount,price_currency curncy, 
					# 1.0 as coeff 
				# from daily_position dp 
				# join pnote_info pi on pi.isin_code=dp.ticker 
				# where ref_date='${DATE}' 
				# and security_type='PNOTE' 
				# and ${PN_TICK_CRITERIA} 
				# ) pos
				# join comm_security_latest_price lp on lp.ticker=pos.p_tk 
					# and lp.ref_date='${PRICE_DATE}'
				# where ${PORTF_CRITERIA} 
				# ''')
	
	#one_day = timedelta(days=1)	
	#cut_of_date = start_date - one_day
	# sql_text = sql_tpl.substitute(DATE=start_date.isoformat(),
							# PRICE_DATE=cut_of_date.isoformat(), 
							# TICK_CRITERIA=format_sql_criteria('trd.ticker', tick_list), 
							# PN_TICK_CRITERIA=format_sql_criteria('pi.underlying_ticker', tick_list), 
							# PORTF_CRITERIA=format_sql_criteria('t.portfolio_id', portf_list))	
	# cursor.execute(sql_text)
	# r = cursor.fetchall()
	
	# sql_text = sql_tpl.substitute(DATE=end_date.isoformat(),
							# PRICE_DATE=end_date.isoformat(), 
							# TICK_CRITERIA=format_sql_criteria('trd.ticker', tick_list), 
							# PN_TICK_CRITERIA=format_sql_criteria('pi.underlying_ticker', tick_list), 
							# PORTF_CRITERIA=format_sql_criteria('t.portfolio_id', portf_list))	
	# cursor.execute(sql_text)
	# r = cursor.fetchall()		

	r = {}
	sql_tpl = Template('''select trd.ticker,
				portfolio_id,
				currency,
				decode(security_type,'FUTURES',s."value",1.0)
					*sum(amount*cost_price*decode(trade_type,'BUY',-1,'SHORT COVERING',-1,'SC',-1,1)) pnl,
				decode(security_type,'FUTURES',s."value",1.0)
					*sum(amount*cost_price*decode(trade_type,'BUY',-1,'SELL',1,0)) long_pnl,
				decode(security_type,'FUTURES',s."value",1.0)
					*sum(amount*cost_price*decode(trade_type,'SHORT SELLING',1,'SHORT COVERING',-1,0)) short_pnl
				from transaction_tick_ex trd
				left join comm_misc_static_info s 
					on trd.ticker=s.ticker 
					and s.field='FUT_VAL_PT' 
				where trade_date>=TO_DATE('${START_DATE}','yyyy-mm-dd') 
					and trade_date<=TO_DATE('${END_DATE}','yyyy-mm-dd')
					and ${TICK_CRITERIA}
					and ${PORTF_CRITERIA} 
					and trd.security_type<>'REPO' 
				group by portfolio_id,trd.ticker,trd.currency,trd.security_type,s."value" ''')
	sql_text = sql_tpl.substitute(START_DATE=start_date.isoformat(), 
							END_DATE=end_date.isoformat(),
							TICK_CRITERIA=format_sql_criteria('trd.lticker', tick_list), 
							PORTF_CRITERIA=format_sql_criteria('trd.portfolio_id', portf_list))	
	
	#print sql_text	
	cursor.execute(sql_text)
	for entry in cursor.fetchall():
		#key:ticker+portf_id+currency
		#val:pnl
		#print entry
		r[(entry[0],entry[1],entry[2])] = [entry[3],entry[4],entry[5]]
	
	#print r
	sql_tpl = Template('''select dp.ticker,
					portfolio_id,
					price_currency curncy,
					decode(security_type,'FUTURES',s."value",1.0)
						*${START_OUT_END_IN}
						*amount
						*lp.close_price pnl, 
					decode(security_type,'FUTURES',s."value",1.0)
						*${START_OUT_END_IN}
						*decode(sign(amount),-1,0,amount)
						*lp.close_price long_pnl,
					decode(security_type,'FUTURES',s."value",1.0)
						*${START_OUT_END_IN}
						*decode(sign(amount),-1,amount,0)
						*lp.close_price short_pnl
				from daily_position_tick_ex dp
				left join comm_misc_static_info s 
					on dp.ticker=s.ticker 
					and s.field='FUT_VAL_PT' 
				left join comm_security_latest_price lp 
					on lp.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
					and dp.lticker=lp.ticker
				where dp.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
					and ${PORTF_CRITERIA} 
					and ${TICK_CRITERIA} 
					and dp.security_type<>'REPO' 
					and dp.security_type<>'CASH' ''')
	
	one_day = timedelta(days=1)	
	cut_of_date = start_date - one_day
	sql_text = sql_tpl.substitute(DATE=cut_of_date.isoformat(),
							TICK_CRITERIA=format_sql_criteria('dp.lticker', tick_list), 
							PORTF_CRITERIA=format_sql_criteria('dp.portfolio_id', portf_list), 
							START_OUT_END_IN=-1.0)	
	cursor.execute(sql_text)
	for entry in cursor.fetchall():
		k = (entry[0],entry[1],entry[2])
		v = [entry[3],entry[4],entry[5]]
		r.setdefault(k, [0.0,0.0,0.0])
		r[k][0] += v[0]
		r[k][1] += v[1]
		r[k][2] += v[2]
		#print 'start_date, entry[3]=', entry[3]
	
	sql_text = sql_tpl.substitute(DATE=end_date.isoformat(),
							TICK_CRITERIA=format_sql_criteria('dp.lticker', tick_list), 
							PORTF_CRITERIA=format_sql_criteria('dp.portfolio_id', portf_list),
							START_OUT_END_IN=1.0)	
	cursor.execute(sql_text)
	for entry in cursor.fetchall():
		k = (entry[0],entry[1],entry[2])
		v = [entry[3],entry[4],entry[5]]
		r[k][0] += v[0]
		r[k][1] += v[1]
		r[k][2] += v[2]
		#print 'end_date, entry[3]=', entry[3]
		
	return [list(k)+v for (k,v) in r.items()]
	
	

	